package com.file.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

@Service
public class ImportExcelUtils {
    private final static String excel2003 =".xls";
    private final static String excel2007 =".xlsx";


    public List<List<Object>> getBankListByExcel(InputStream in, String fileName){
        List<List<Object>> list = null;
        Workbook work = null;
        //创建Excel工作薄
        work = this.getWorkbook(in, fileName);
        //表中的sheet
        Sheet sheet = null;
        //行数
        Row row = null;
        //列数
        Cell cell = null;


        try{


        if(null == work){
            return null;
        }

        list = new ArrayList<List<Object>>();

        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            if(sheet==null){
                continue;
            }

            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                row = sheet.getRow(j);
                if(row==null||row.getFirstCellNum()==j){continue;}

                List<Object> li = new ArrayList<Object>();
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                    cell = row.getCell(y);
                    li.add(cell);
                }
                list.add(li);
            }
        }
        work.close();
        }catch (IOException e){
            throw new RuntimeException("创建EXECL表示失败");
        }
        return list;

    }

    /**
     * 判断excel文件的格式
     * @param in
     * @param fileName
     * @return
     */
    private Workbook getWorkbook(InputStream in, String fileName) {

        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
            try{
                if(excel2003.equals(fileType)){
                    //使用.xls的方法
                    wb = new HSSFWorkbook(in);
                }else if(excel2007.equals(fileType)){
                    //使用xlsx
                    wb = new XSSFWorkbook(in);
                }else{
                    throw new RuntimeException("解析的文件格式不存在");
                }
            }catch (IOException e){
                throw new RuntimeException("解析的文件格式有误！");
            }

            return wb;
    }


}
